<?php
/**
 * Sheet 类
 * @author: xiao<admin@end.wiki>
 * @time: 2017-10-08 15:21
 */
 
namespace app;

use core\Addition;
use exception\CreateTableHasFailureException;
use exception\FileTypeOrDataErrorException;
use exception\NotFoundDataException;
use exception\NotNumericException;
use exception\OutputColumnNotFoundException;
use exception\ExcelNotUploadException;
use exception\DataQueryHasFailureException;
use exception\NotFoundTopComputeColumnException;
use exception\TopFilterThanTwoException;
use exception\DataInsertIntoException;
use exception\InputValueMustBeNumeric;

class Sheet {
	// MySQLi 句柄
	private $mysqli;
	private $mysqli2;
	private $customCategorys;		// 自定义的类目
    private $title;
	private $conditions;			// 过滤条件
	private $limit;
	private $tempTableFieldsCount;
	private $fields;
	private $orderColumn;           // 排序列
    private $objWriter;             // 目前仅供[程序1]使用
    private $titleOfProgramOne;     // 保存[程序1]的标题
    private $fieldsCount = 0;
    private $titleWriteFlagOfProgramOne = false;        // [程序1]标题写入旗帜
    private $topValue;                  // 为了优化速度，保存 Top 计算的结果

	
	// [程序1][程序2]预定义表字段
private $preDefineField = ['a','b','c','d','e','f','g','h','i','j','k',
		'l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','aa','ab',
		'ac','ad','ae','af','ag','ah','ai','aj','ak','al','am','an','ao',
		'ap','aq','ar','zs','zt','au','av','aw','ax','ay','az','ba','bb',
		'bc','bd','be','bf','bg','bh','bi','bj','bk','bl','bm','bn','bo',
		'bp','bq','br','bs','bt','bu','bv','bw','bx','zy','bz','ca','cb','cc','cd','ce','cf','cg','ch',
        'ci','cj','ck','cl','cm','cn','co','cp','cq','cr','cs','ct','cu','cv','cw','cx','cy','cz',
        'da','db','dc','dd','de','df','dg','dh',
        'di','dj','dk','dl','dm','dn','do','dp','dq','dr','ds','dt','du','dv','dw','dx','dy','dz',
        'ea','eb','ec','ed','ee','ef','eg','eh',
        'ei','ej','ek','el','em','en','eo','ep','eq','er','es','et','eu','ev','ew','ex','ey','ez'];
	
	
	// [程序1][程序2]构造
	public function __construct(){
		// 查看 README.md 中特殊的写法中的两个 MySQLi 连接 
		$this->mysqli = new \mysqli('localhost','root','','zhongwen',3306);
		$this->mysqli2 = new \mysqli('localhost','root','','zhongwen',3306);

		$this->mysqli->set_charset("utf8");
		$this->mysqli->set_charset("set global max_allowed_packet = 500*1024*1024");
		$this->mysqli2->set_charset("utf8");
		$this->mysqli2->set_charset("set global max_allowed_packet = 500*1024*1024");
		
		if(mysqli_connect_error()){
			throw new \Exception('MySQL connect failure!');
		}
	}

	// [程序1]清空数据
	private function clear(){
	    $sql = 'DROP TABLE test;';
        $this->mysqli->query($sql);
        $sql = 'TRUNCATE TABLE title;';
        $this->mysqli->query($sql);
        $sql = 'TRUNCATE TABLE title2;';
        $this->mysqli->query($sql);
        $sql = 'TRUNCATE TABLE temp_compute;';
        $this->mysqli->query($sql);
    }
	
	// [程序1]将 Excel 导入数据库
	public function insertDb($filePath,$objReader){
	    // 清除所有数据
        $this->clear();
		// 检查文件路径是否存在
		if(!file_exists($filePath)){
			throw new \Exception('Excel File not found!');
		}
		// 检查文件格式
		if('xlsx' != pathinfo($filePath,PATHINFO_EXTENSION)){
            throw new FileTypeOrDataErrorException();
        }
		// 打开 Excel 文件
		try{
			$objReader->open($filePath);
		}catch(\Exception $e){
			throw new FileTypeOrDataErrorException();
		}
		
		/* ------------------- */
		/*      写入数据库       */
		/* ------------------- */
		
		// 遍历 Sheet,获取第一张表
		$rowIterator = $this->getFirstSheet($objReader);
		if(false === $rowIterator){
			throw new \Exception('Excel sheet not found!');
		}
		// 遍历 row,写入数据库
		$insertRowSize = 0;
		$insetDataValue = '';
		foreach($rowIterator as $row){
			$insertRowSize++;
			$preDefineField = $this->preDefineField;
			// 第一行为标题,写入 title 表
			if(1 === $insertRowSize){
				$insertTitle = '';
				foreach($row as $column){
                    $this->fieldsCount++;           // 字段数自增长
					$insertTitle .= ',(\'' . $column . '\')';
				}
				$insertTitleSql = 'INSERT INTO title (title) VALUES ' . substr($insertTitle,1) .';';
				$this->mysqli->query($insertTitleSql);
			}else{
                // 创建表结构
                $this->createTempTable('test',$this->fieldsCount,$row);
                // 写入 row 到数据表
                $escapeRow = [];
                foreach($row as $value){
                    $escapeRow[] = addslashes($value);
                }
                $insetDataValue .= ',(\'' . implode('\',\'', $escapeRow) . '\')';
                if($insertRowSize % 100 == 0 ){
                    $temp = array_splice($preDefineField,0,count($row));
                    $insertDataSql = 'INSERT INTO test ('
                        . implode(',',$temp)
                        . ') VALUES ' . substr($insetDataValue, 1) . ';';
                    $result = $this->mysqli->query($insertDataSql);
                    if($result === false){
                        add_error_log(502,$insertDataSql,__FILE__,__LINE__);
						throw new DataInsertIntoException('第 ' . $insertRowSize . ' 行附近数据有异常!');
                    }
                    $insetDataValue = '';
                }
            }
		}
	}

	// [程序1]分组并输出 Excel
	public function groupAndOutput($distinctColumn,$outColumns,$objWriter,$outputPath){
		// 检查数据是否存在
		$isUpload = $this->isUploadExcel();
		if(false === $isUpload){
			throw new ExcelNotUploadException();
		}
		// 从数据库中查询分组数据
		$groupDataResult = $this->getGroupDataFromDb($distinctColumn,$outColumns);
		if(false == $groupDataResult){
			throw new \Exception('Query group data has failure!',201);
		}
        if(!isset($this->objWriter)){
            $this->objWriter = $objWriter;
        }

        $this->objWriter->openToFile($outputPath);
		// 写入 title2 表，以备程序2使用
        // TODO: 这一句可以去掉
		// $title = $this->getTitle();

        // 对分组数据算法过滤并输出到 Excel
		$fields = [];
        $computeCategoryValues = [];
		$sheetData = [];
		foreach($outColumns as $item => $value){
            $fields[] = $value['column'];           // 获取所有输出字段
			// 替换关键字
			$value['column'] = replace_primary_token($value['column']);
			$outColumns[$item]['column'] = $value['column'];
			
            switch($value['compute_type']){
                case 'raw' :
                    break;
                case 'avg' :
                    // 求平均值
                    $computeCategoryValues[$value['column']] = $this->getAvgOfColumn($distinctColumn,$value['column']);
                    break;
                case 'sum' :
                    // 求和
                    $computeCategoryValues[$value['column']] = $this->getSumOfColumn($distinctColumn,$value['column']);
                    break;
                case 'first' :
                    // 求第一天的值
                    $computeCategoryValues[$value['column']] = $this->getFirstDayOfColumn($distinctColumn,$value['column'],$value['date_column']);
					break;
                case 'last' :
                    // 求最后一天的值
                    $computeCategoryValues[$value['column']] = $this->getLastDayOfColumn($distinctColumn,$value['column'],$value['date_column']);
                    break;
                case 'last-first' :
                    // 求一最后一天的值减去第天的值
                    $firstDayValue[$value['column']] = $this->getFirstDayOfColumn($distinctColumn,$value['column'],$value['date_column']);
                    $lastDayValue[$value['column']] = $this->getLastDayOfColumn($distinctColumn,$value['column'],$value['date_column']);
                    break;
                case 'max' :
                    // 求最大值
                    $computeCategoryValues[$value['column']] = $this->getMaxOfColumn($distinctColumn,$value['column']);
                    break;
                case 'min' :
                    // 求最小值
                    $computeCategoryValues[$value['column']] = $this->getMinOfColumn($distinctColumn,$value['column']);
                    break;
                case 'max-min' :
                    // 求最大值减去最小值
                    $maxValue[$value['column']] = $this->getMaxOfColumn($distinctColumn,$value['column']);
                    $minValue[$value['column']] = $this->getMinOfColumn($distinctColumn,$value['column']);
                    break;
                default :
                    throw new \Exception('Column type is not found!');
            }
        }

        $rowSize = 0;
		$this->title = $this->getTitle();
		$titleFlag = [];
		$titleIndex = 0;
        while($row = mysqli_fetch_array($groupDataResult,MYSQLI_ASSOC)){
			$outColumnsFlag = [];			// 重复列旗标
            foreach($outColumns as $item => $column){
				// 如果是重复列则跳过
				if(!isset($outColumnsFlag[$column['column']])){
					$outColumnsFlag[$column['column']] = true;
				}else{
					continue;
				}
				// 获取标题
				if(!isset($titleFlag[$column['column']])){
					$titleFlag[$column['column']] = true;			// 设置旗标
                    ($this->titleOfProgramOne)[$titleIndex] = $this->title[array_search($column['column'],$this->preDefineField)] . '(' . $column['compute_type'] . ')';
					$titleIndex++;
				}
				if($column['compute_type'] == 'max-min'){
					$max = $maxValue[$column['column']][$row[$distinctColumn]];
					$min = $minValue[$column['column']][$row[$distinctColumn]];
					$sheetData[$rowSize][] = $max - $min;
					continue;
				}else if($column['compute_type'] == 'last-first'){
					$firstValue = $firstDayValue[$column['column']][$row[$distinctColumn]];
					$lastValue = $lastDayValue[$column['column']][$row[$distinctColumn]];
					$sheetData[$rowSize][] = $lastValue - $firstValue;
					continue;
				}
                if(!isset($computeCategoryValues[$column['column']] )){
					$sheetData[$rowSize][] = $row[$column['column']];
                }else{
					if(!isset($computeCategoryValues[$column['column']][$row[$distinctColumn]])){
                        $sheetData[$rowSize][] = 0;
                    }else{
                        $sheetData[$rowSize][] = $computeCategoryValues[$column['column']][$row[$distinctColumn]];
                    }
                }
            }
            yield $sheetData[$rowSize];
            $rowSize++;
        }
	}

	// [程序1]二次计算后写入 Excel
	public function writerExcel($rowIterator,$secondComputeParams){
        // 迭代写入 Excel 数据
        foreach($rowIterator as $item => $row){
            // 二次计算
            foreach($secondComputeParams as $params){
                // 计算结果并写入表格行数组
                // XXX: 预计 PHP7 对 use 关键字的优化，导致了动态(在字符串中保存的类名)无法引入,故采用如下写法
                $algorithmClass = 'core\\' . $params['compute_type'];
                $computeClass = new $algorithmClass;
				// 如果用户输入的不是数字,则抛出异常	
				if(!is_numeric($params['column_a']) || !is_numeric($params['column_b'])){
					throw new InputValueMustBeNumeric();
				}
                $row[] = $computeClass->compute([$row[$params['column_a'] - 1],$row[$params['column_b'] - 1]]);
                // 获取标题写入表格标题行数组
                $this->titleOfProgramOne[] = ($this->titleOfProgramOne)[$params['column_a'] - 1]
                    . ' ' . $computeClass->label . ' ' . ($this->titleOfProgramOne)[$params['column_b'] - 1];
                // 因为数据量大，所以主动销毁类的实例
                unset($computeClass);
            }
            // 判断标题写入旗帜是否为假，如果为假写入标题
            if(!$this->titleWriteFlagOfProgramOne){
                $this->titleWriteFlagOfProgramOne = !$this->titleWriteFlagOfProgramOne;
                $this->objWriter->addRow($this->titleOfProgramOne);
            }
            // 写入 Excel
            $this->objWriter->addRow($row);
        }
        // 关闭 Excel 连接
        $this->objWriter->close();
    }
	
	// [程序2]过滤并输出 Excel
	public function filterAndOutput($categoryColumn,$customCategorys,$conditions,$outColumns,	$isOutSumColumn,
            $inputFilePath,	$outputFilePath,$objReader,$objWriter,$secondComputeParams){

	    // 设置自定义类目
		$this->setCustomCategory($customCategorys);
		// 遍历 Excel,写入临时表
        $this->eachExcelInsertTable($objReader,$inputFilePath,$categoryColumn);
        // 过滤数据并得到数据的迭代器
        $rowIterator = $this->filterData($outColumns,$conditions,$customCategorys,$categoryColumn);
        // 处理数据并输出到 Excel
        $this->exportExcel($rowIterator,$outColumns,$objWriter,$outputFilePath,$isOutSumColumn,$secondComputeParams);

	}

    // [程序2]处理数据并输出到 Excel
    private function exportExcel($rowIterator,$outColumns,$objWriter,$outputFilePath,$isOutSumColumn,$secondComputeParams){
	    $this->objWriter = $objWriter;
		// 导出到 Excel
        $this->objWriter->openToFile($outputFilePath);
        $sheetRow = [];         // 表格数据
        $additionClass = new Addition();
        // 遍历二次相加的列
        foreach($secondComputeParams as $item => $param){
            $secondComputeColumn[$item] = explode(',',$param['addend_column']);
        }
        foreach($rowIterator as $rowItem => $row) {
            // 遍历列
            $columnIndex = 0;             // 当前列数
            $weights = [];
            $sum = 0;
            foreach ($row as $colItem => $col) {
                // 跳过自定义类目字段
                if(!isset($outColumns[$columnIndex])){
                    break;
                }
                // 记录下权重以备二次计算
                $weights[] = $outColumns[$columnIndex]['weight'];
				

                if( isset($outColumns[$columnIndex]) && 'sum' === $outColumns[$columnIndex]['alogrithmic'] ){
                    // 相加算法
                    $additionColumns = explode(',',$outColumns[$columnIndex]['column']);
                    foreach($additionColumns as $item => $column){
                        if(!is_numeric($column)){
                            throw new NotNumericException('您输入的输出列在第 ' . $columnIndex . ' 行填入了非数值,因而出现了错误。');
                        }
						$index = (array_keys($row))[$column  - 1];
                        $additions[] = $row[$index];
                        // $sumTitle[] = $this->title[array_search($column,$this->preDefineField)];
                    }
                    $value = $additionClass->compute($additions);

                    $sheetRow[] = $value;
					
                    $additions = [];             // 清空加数
                    // 为了数据
                    $columnIndex++;
                }

	
				
                // 采用顶部算法
                if (isset($outColumns[$columnIndex]) &&  'top' === $outColumns[$columnIndex]['alogrithmic'] && isset($outColumns[$columnIndex])) {
                    // 顶部算法
                    $customCategoryField = (array_keys($row))[(count(array_keys($row)) - 1)];
                    $customCategoryValue = $row[$customCategoryField];

                    $topValue = $this->top($col,$outColumns[$columnIndex]['column'],$customCategoryField,$customCategoryValue);
					$sheetRow[] = $topValue;

                    // 判断是否保留原值
                    if (true === $outColumns[$columnIndex]['isOutRawColumn']) {
                        $sheetRow[] = $col;
                    }
                }elseif( isset($outColumns[$columnIndex]) && 'raw' === $outColumns[$columnIndex]['alogrithmic'] && isset($outColumns[$columnIndex])) {

                    $sheetRow[] = $col;
                }
                // 保留二次计算的值

                foreach($secondComputeColumn as $w => $x){
                    if(in_array($columnIndex + 1,$x)){
						// 没有找到采用 Top 算法的列
						if(!isset($topValue)){
							$computeValue = $col;
						}else{
							$computeValue = $topValue;
						}
                        $secondComputeAdditions[$w][] = $computeValue * ($outColumns[$columnIndex]['weight'] / 100);
//                            echo '-------------' . '<br />';
//                            var_dump($row);
//                            echo '$w:' . $w . '<br />';
//                            echo '$topValue:' . $topValue . '<br />';
//                            echo '$columnIndex:' . ($columnIndex) . '(' . $colItem . ')' . '<br />';
//                            echo '$outColumns[$columnIndex][weight]:' . $outColumns[$columnIndex]['weight'] . '<br />';
//                            echo 'addition:' . ($topValue * ($outColumns[$columnIndex]['weight'] / 100)) . '<br />';
//                            echo '-------------' . '<br />';
                    }
                }
                $columnIndex++;
                $weights = [];
            }
            // 二次计算相加
            foreach($secondComputeAdditions as $y => $z){
                $sheetRow[] = $additionClass->compute($z);
            }

            $secondComputeAdditions = [];

            // 输出标题
            if(!$this->titleWriteFlagOfProgramOne){
                $sheetTitle = [];       // 表格标题
                foreach($outColumns as $columnIndex => $column){
					if(isset($this->title[array_search($column['column'],$this->preDefineField)])){
						$titleTemp = $this->title[array_search($column['column'],$this->preDefineField)];
					}else{
						$titleTemp = ' UN-KNOW ';
					}
                    switch($column['alogrithmic']){
                        case 'raw':
                            $sheetTitle[] = $titleTemp . '(Raw)';
                            break;
                        case 'top':
                            $sheetTitle[] = $titleTemp . '(Top)';
                            if($column['isOutRawColumn'] == 'true'){
                                $sheetTitle[] = $titleTemp . '(TopAndRaw)';
                            }
                            break;
                        case 'sum':
                            $sheetTitle[] = 'SUM';
                            break;
                        default:
                    }
                }
                // 二次计算中添加的标题
                foreach($secondComputeColumn as $item => $value){
                    $tempTitle = '';
                    foreach($value as $yy => $zz){
                        $index = array_search($outColumns[$zz - 1]['column'],$this->preDefineField);
                        $tempTitle .= '+' . $this->title[$index];
//                        echo '----------------' . '<br />';
//                        echo '$zz + 1:' . ($zz - 1) . '<br />';
//                        echo '$outColumns[$zz - 1][column]:' . $outColumns[$zz - 1]['column'] . '<br />';
//                        echo '$index:' . $index . '<br />';
//                        echo '$this->title[$index]:' . $this->title[$index] . '<br />';
//                        echo '----------------' . '<br />';
                    }
                    $sheetTitle[] = substr($tempTitle,1);
                }

                $objWriter->addRow($sheetTitle);
                $this->titleWriteFlagOfProgramOne = !$this->titleWriteFlagOfProgramOne;
            }

            // 输出行数据
            $this->objWriter->addRow($sheetRow);
			$sheetRow = [];		// 清空行数据数组
        }
        $this->objWriter->close();
    }

    // [程序2]顶部算法
    private function top($col,$column,$customCategoryField,$customCategoryValue){
        // 根据用户自定义分类求最大值
		$limit = ' LIMIT 1';
		if(isset($this->limit)){
			$limit = $this->limit;
		}
		$sql = '';
        // 保存最大值的查询结果，避免重复查询
        if(!isset($this->topValue[$column][$customCategoryField][$customCategoryValue])){
			// 判断条件是否为空
			if($this->conditions == ''){
				$sql = 'SELECT max(' . $column . ') FROM temp_compute WHERE ' .
					$customCategoryField . ' =' . ($customCategoryValue) .  ' limit ' 
					. $limit;
			}else{
				$sql = 'SELECT max(' . $column . ') FROM temp_compute WHERE ' .
                $customCategoryField . ' =\'' . ($customCategoryValue) .  '\'' . $this->conditions
                . $limit;
			}
	
			$result = $this->mysqli2->query($sql);
			if(false == $result){
				add_error_log(10001,$sql,__FILE__,__LINE__);
				throw new DataQueryHasFailureException();
			}
            $this->topValue[$column][$customCategoryField][$customCategoryValue] = mysqli_fetch_array($result)[0];

        }

        $maxValue = $this->topValue[$column][$customCategoryField][$customCategoryValue];
		if($maxValue != '0'){
			// 防止除数为0
			$topValue = 0;
			if(intval($maxValue) != 0){
				// 当前值乘以自定义类目中的最大值后除以系数(10000)
				$topValue = (intval($col) / intval($maxValue)) * 10000;

			}
        }else{
		    $topValue = 0;
        }

        return $topValue;
    }

    // [程序2]过滤数据
	private function filterData($outColumns,$conditions,$customCategorys,$categoryColumn){
		$categorys = '';
		foreach($customCategorys as $value){
			$categorys .= $value . ',';
		}

        // 拼接字段
        $fields = [];
		if(!is_array($outColumns)){
			var_dump($outColumns);
			die();
		}

		
        foreach($outColumns as $column){
            // 跳过以逗号分割的输出列
            if(!strpos($column['column'],',')){
                $fields[] = $column['column'];
            }
        }

	    // 拼接条件
        $where = '';
        $topNum = 0;
        foreach($conditions as $condition){

            if('top' === $condition['operator']){
                // 重复输入了 TOP 条件
                if($topNum >= 1){
                    throw new TopFilterThanTwoException();
                }
				$this->limit = $condition['value'];
                $this->orderColumn = $condition['column'];
                $topNum++;
            }else{
                $where .= ' AND ' . $condition['column'] . ' ' . $condition['operator'] . ' ' . $condition['value'];
            }
        }

		// $where .= ' AND ' . $categoryColumn . ' IN (\'' .
			// implode('\',\'',explode(',',substr($categorys,0,strlen($categorys)-1))) .'\')';
		$this->conditions = $where;		// 将条件设为类成员变量，以备后续使用

        // 加入自定义类目字段
        $customCategoryField = $this->preDefineField[$this->tempTableFieldsCount - 1];
        array_push($fields,$customCategoryField);

        if(isset($this->limit)){
            $sql = '';
            // 根据自定义类目进行 TOP 过滤
            foreach($this->customCategorys as $item => $value){
                $sql .= ' UNION (' . 'SELECT ' . implode(',',$fields) . ' FROM temp_compute WHERE ' .
                    $customCategoryField . ' = ' . $item  . $where .
                    ' ORDER BY ' . $this->orderColumn . ' DESC  LIMIT ' . $this->limit . ')';
            }
            $sql = substr($sql,7) . ';';
        }else{
			$customCategories = [];
			foreach($this->customCategorys as $item => $value){
				$customCategories[] =  $item;
			}
			// 自定义类目大于0过滤掉非自定义类目
            $sql = 'SELECT ' . implode(',',$fields) . ' FROM temp_compute WHERE '
                . substr($where,4) . ' AND ' . $customCategoryField  . ' >  0  ORDER BY ' . $condition['column'] . ' DESC ';
        }

		$result =  $this->mysqli->query($sql . ';');

		if($this->mysqli->affected_rows === 0){
		    add_error_log($this->mysqli->errno,$sql,__FILE__,__LINE__);
		    throw new DataQueryHasFailureException('不好意思,没有查询到对应的数据,请检查您的输入内容和过滤条件!');
        }


        return $result;
    }

    // [程序2]遍历 Excel,写入临时表
	private function eachExcelInsertTable($objReader,$inputFilePath,$categoryColumn){
        $objReader->open($inputFilePath);
        $rowIterator = $this->getFirstSheet($objReader);
        $preDefineField = $this->preDefineField;
        $insertRowData = '';
		$createTempTableFlag = false;
        foreach($rowIterator as $item => $row) {
            // 获取列的标题
            if ($item === 1) {
				$firstRowFieldCount = count($row);
                $this->title = $row;
                $fields = '`' . implode('`,`',(array_splice($preDefineField,0,count($this->title) + 1))) . '`';
                continue;
            }
			// 检测字段数可能不足的情况
			if(count($row) < $firstRowFieldCount){

				$less = $firstRowFieldCount - count($row);
				for($i = 1;$i <= $less;$i++){
					$row[] = '';
				}
			}
            // 插入自定义类目字段
			if(!isset($row[array_search($categoryColumn,$this->preDefineField)])){
				var_dump($row);
			}
            $oldCategory = $row[array_search($categoryColumn,$this->preDefineField)];
            array_push($row,$this->getCustomCategory($oldCategory));
			
            $this->tempTableFieldsCount = count($row);
			// 创建临时表
			if(1 != $item && !$createTempTableFlag){
				$this->createTempTable('temp_compute',count($this->title) + 1,$row);
				$createTempTableFlag = !$createTempTableFlag;
			}
			// 数据转义
			$escapeRow = [];
			$flag = [];
			foreach($row as $fieldIndex => $field){
				// if($field == '' || $field == 0){
					// $flag[$fieldIndex] = false;
				// }
				//$escapeRow[] = mysqli_escape_string($this->mysqli,$field);
				$escapeRow[] = addslashes($field);
			}
			
			// foreach($flag as $flagValue){
				// if($flagValue){
					// $comFlag = true;
				// }
			// }
			$insertRowData .= ',(\'' . implode('\',\'',$escapeRow) . '\')';
            if($item % 1000 === 0){
				// 检测空行情况
				// if(is_null(substr($insertRowData,1)) || substr($insertRowData,1) == ''){
					// continue;
				// }
				if(false == substr($insertRowData,1) ){
					continue;
				}
                $sql = 'INSERT INTO temp_compute (' . $fields .') VALUES '
                    . substr($insertRowData,1) . ';';
					$result = $this->mysqli->query($sql);
               
				if($result == false){
                    add_error_log($this->mysqli->errno,$sql,__FILE__,__LINE__);
                    throw new DataInsertIntoException();
				}
                $insertRowData = '';                // 清空插入数据
                continue;
            }
        }
		if(false == substr($insertRowData,1) ){
			
		}else{
			        $sql = 'INSERT INTO temp_compute (' . $fields .') VALUES '
            . substr($insertRowData,1) . ';';

        $result = $this->mysqli->query($sql);

			if(false === $result){
				add_error_log($this->mysqli->errno,$sql,__FILE__,__LINE__);
				throw new CreateTableHasFailureException();
			}
			
		}

    }

    // [程序2]获取自定义的类目的代码
	private function getCustomCategory($oldCategory){
	    foreach($this->customCategorys as $item => $value){
	        if(in_array($oldCategory,explode(',',$value))){
                return $item + 1;			// 为了避免默认值
            }
        }
        return false;
    }

	// [程序2]根据字段数创建临时表
	private function createTempTable($tableName,$fieldsCount,$row = null){
	    $preDefineField = $this->preDefineField;
	    // +1 是因为自定义类目字段
	    $fields = array_splice($preDefineField,0,$fieldsCount);
	    $sql = 'CREATE TABLE IF NOT EXISTS  ' . $tableName .' ( ';
        $this->fields = $fields;
        // 如果行数据存在，则检测数据类型
        if(is_null($row)){
            $fields = array_map(function($value){
                return ($value . ' VARCHAR(100) NOT NULL DEFAULT \'\'');
            },$fields);
        }else{
            foreach($fields as $item => $value){
                if(!isset($row[$item])){
                    // 如果不存在,则返回默认值
                    $fields[$item] = '`' . $value . '`'  . check_type('RETURN DEFAULT VALUE') ;
                }else{
                    $fields[$item] = '`' . $value . '`'  . check_type($row[$item]) ;
                }
            }
        }

        $sql .= implode(',',$fields) . ')ENGINE=MEMORY CHARACTER SET \'utf8\';';

        $result = $this->mysqli->query($sql);
        if(false === $result){
            add_error_log($this->mysqli->errno,$sql,__FILE__,__LINE__);
            throw new \Exception('Temporary table create failure!');
        }

	}
	
	// [程序2]设置自定义类目
	private function setCustomCategory($customCategorys){
		$this->customCategorys = $customCategorys;
	}
	
	// 获取标题
	private function getTitle(){
		$sql = 'SELECT title FROM title WHERE 1=1;';
		$result = $this->mysqli2->query($sql);
		if(false == $result){
            add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
			return false;
		}
		while ( $row = mysqli_fetch_array($result,MYSQLI_NUM)){
			$title[] = $row[0];
		}
		return $title;
	}
	
	// [程序2]检查是否上传文件
	private function isUploadExcel(){
		$sql = 'SELECT * FROM title WHERE 1=1 LIMIT 1';
		$result = $this->mysqli2->query($sql);
		if($this->mysqli2->affected_rows === 0){
		    add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
			return false;
		}
		return true;
	}

	// [程序1]从数据库中查询分组数据
	private function getGroupDataFromDb($distinctColumn,$outColumns){
		foreach($outColumns as $item => $value){
			// 替换关键字
			$value['column'] = replace_primary_token(strtolower($value['column']));

			// 检查用户输入的列是否都有效
			if(!in_array($value['column'],$this->preDefineField)){
				throw new OutputColumnNotFoundException('您指定输出的列 [ ' . $value['column'] . ' ] 不存在!' );
			}
			$outFields[] = $value['column'];
		}

		$dataQuerySql = 'SELECT ' 
			. implode(',',$outFields) 
			. ' FROM test WHERE 1=1 GROUP BY ' 
			. $distinctColumn . ' HAVING ' . $distinctColumn . ' <> \'\';';

		$result = $this->mysqli->query($dataQuerySql,MYSQLI_USE_RESULT);
		if($result == false){
            add_error_log($this->mysqli->errno,$dataQuerySql,__FILE__,__LINE__);
            throw new DataQueryHasFailureException('分组数据查询异常,请检查过滤条件');
        }

		return $result;			// 返回查询结果的句柄
	}
	
	// 遍历 Sheet,获取第一张表
	private function getFirstSheet($objReader){
		foreach($objReader->getSheetIterator() as $sheet){
			if( $sheet->isActive()){
				$rowIterator = $sheet->getRowIterator();
				return $rowIterator;
			}
		}
		return false;
	}

	
	// [程序2]获取列的平均值
	private function getAvgOfColumn($distinctColumn,$curColumn){
		$sql = 'SELECT ' . $distinctColumn . ',avg(' . $curColumn . ') as avg_value FROM test WHERE 1=1 GROUP BY `' . $distinctColumn . '`;';
		add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
		$result = $this->mysqli2->query($sql);
		if(false === $result){
		    add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
		    throw new DataQueryHasFailureException('平均值查询异常,请检查数据!');
        }
		$index = 0;
		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            $value[$row[$distinctColumn]] = $row['avg_value'];
            $index++;
        }
		return $value;
	}
	
	// [程序2]获取列的和
	private function getSumOfColumn($distinctColumn,$curColumn){
		$sql = 'SELECT ' . $distinctColumn . ',sum(' . $curColumn . ') as avg_value FROM test WHERE 1=1 GROUP BY `' . $distinctColumn . '`;';
        $result = $this->mysqli2->query($sql);
        if(false === $result){
            add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
            throw new DataQueryHasFailureException();
        }
		$index = 0;
		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            $value[$row[$distinctColumn]] = $row['avg_value'];
            $index++;
        }
		return $value;
	}
	
	// [程序2]获取列的第一天的值
	private function getFirstDayOfColumn($distinctColumn,$curColumn,$dateColumn){
        $sql = 'SELECT test.' . $dateColumn . ',test.' . $distinctColumn . ',MAX(' . $curColumn . ')
		     as max_value FROM test JOIN (SELECT ' . $distinctColumn . ',MIN(' . $dateColumn . ') as a FROM test 
		     GROUP BY ' . $distinctColumn . ') AS temp WHERE temp.' . $distinctColumn . '
		      = test.' . $distinctColumn . ' AND temp.' . $dateColumn . ' = test.' . $dateColumn
            . ' GROUP BY test.' . $distinctColumn;
		$result = $this->mysqli2->query($sql);
		if(false === $result){
            add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
            throw new DataQueryHasFailureException();
        }

		$index = 0;

		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            $value[$row[$distinctColumn]] = $row['max_value'];
            $index++;
        }
		return $value;
	}
	
	// [程序2]获取列的最后一天的值
	private function getLastDayOfColumn($distinctColumn,$curColumn,$dateColumn){
        $sql = 'SELECT test.' . $dateColumn . ',test.' . $distinctColumn . ',max(' . $curColumn . ')
		     as min_value FROM test JOIN (SELECT ' . $distinctColumn . ',MAX(' . $dateColumn . ') as a FROM test 
		     GROUP BY ' . $distinctColumn . ') AS temp WHERE temp.' . $distinctColumn . '
		      = test.' . $distinctColumn . ' AND temp.' . $dateColumn . ' = test.' . $dateColumn
            . ' GROUP BY test.' . $distinctColumn;
		$result = $this->mysqli2->query($sql);
		if(false === $result){
            add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
            throw new DataQueryHasFailureException();
        }
		$index = 0;
		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            $value[$row[$distinctColumn]] = $row['min_value'];
            $index++;
        }
		return $value;
	}
	
	// [程序2]获取列的最小值
	private function getMinOfColumn($distinctColumn,$curColumn){
		$sql = 'SELECT ' . $distinctColumn . ',MIN(' . $curColumn . ') as avg_value FROM test WHERE 1=1 GROUP BY `' . $distinctColumn . '`;';
		$result = $this->mysqli2->query($sql);
		if(false === $result){
            add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
            throw new DataQueryHasFailureException();
        }
		$index = 0;
		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            $value[$row[$distinctColumn]] = $row['avg_value'];
            $index++;
        }
		return $value;
	}
	
	// [程序2]获取列的最大值
	private function getMaxOfColumn($distinctColumn,$curColumn){
		$sql = 'SELECT ' . $distinctColumn . ',MAX(' . $curColumn . ') as avg_value FROM test WHERE 1=1 GROUP BY `' . $distinctColumn . '`;';
		$result = $this->mysqli2->query($sql);
		if(false === $result){
            add_error_log($this->mysqli2->errno,$sql,__FILE__,__LINE__);
            throw new DataQueryHasFailureException();
        }
		$index = 0;
		while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
            $value[$row[$distinctColumn]] = $row['avg_value'];
            $index++;
        }
		return $value;
	}
	
	// [程序1][程序2]析构
	public function __destruct(){
	    // 清空临时表
        $this->mysqli->query('DROP TABLE `temp_compute`');
		// 销毁 MySQLi 对象
        mysqli_close($this->mysqli);
        mysqli_close($this->mysqli2);
	}
	
}